package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.LogFactory;

import com.seeyon.ctp.util.UUIDLong;

import fy.config.FyConfig;
import fy.uap.bean.FinanceInvalidSupplierBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class InvalidFinanceSupplierDao {

	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(InvalidFinanceSupplierDao.class);

	private static final String STOPINVCLASS = "update fy_supplier_vendor_invclass v set v.denddate = ? where v.pk_corp = ? and v.pk_vendor = ( select v1.pk_vendor from fy_supplier_vendor v1 where v1.cvencode = ? and (v1.dr is null or v1.dr = 0) ) and v.pk_invclass = (select i.pk_invclass from fy_supplier_invclass i where i.cinvclasscode = 'NONE' and (i.dr is null or i.dr = 0))";

	private static final String LOGSQL = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate, creater) select ?, (select v.pk_vendor from fy_supplier_vendor v where v.cvencode = ? and (v.dr='0' or v.dr is null) and v.cvendefine1 = 'financial'), ?, (select i.pk_invclass from fy_supplier_invclass i where i.cinvclasscode = 'NONE' and (i.dr is null or i.dr = 0)), ?, ?, ?, ?, ?, ?, ?, ? from dual";

	private static final String ERPDELETESQL = "delete from apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ " i where i.VENDORS_NUM = ? and i.UAPLB = 'NONE'  and i.ZT<>'客户' ";

	// private static final String ERPINSERTSQL =
	// "insert into apps.FYG_PO_UAP_VENDORS@"
	// + OADBConf.UAP_DBLINK_ERP
	// +
	// "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag,dx) select v.cvencode,v.cvenname, c.unitname, i.cinvclasscode,         decode(vi.cvendorclass,'1','合格','2','潜在','3','黑名单',vi.cvendorclass,vi.cvendorclass),         to_date(vi.denddate,'YYYY-MM-DD'),'Y'    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass   where v.cvencode = ? and i.cinvclasscode='NONE' and (vi.dr is null or vi.dr = 0) and  vi.cvendorclass <>'3' and (vi.denddate is null or to_date(vi.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ))";

	private static final String ERPINSERTSQL = "insert into apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag ) select * from (select v.cvencode cvencode,  v.cvenname cvenname,  c.unitname unitname,  i.cinvclasscode cinvclasscode,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',decode(vi.cvendorclass,'1','合格',  '2',  '潜在',  '3',  '黑名单',  vi.cvendorclass)) zt,  to_date(vi.denddate, 'YYYY-MM-DD') disable_date,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'N',decode(vi.cvendorclass,  '3',  'N',  'Y')) flag,    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where v.cvencode=? and i.cinvclasscode='NONE'  and (vi.dr is null or vi.dr = 0) ) t where not exists ( select *    from apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ " e where t.cvencode = e.VENDORS_NUM and e.SYB = t.unitname and e.UAPLB=t.cinvclasscode and t.zt = e.zt and (t.disable_date is null and e.disable_date is null or t.disable_date = e.disable_date) ) ";

	private List<FinanceInvalidSupplierBean> beans;

	private String number;

	// private String applicationTime;

	private String memberCode;

	private final static String uapUserID = "0001AA10000000009KX7";

	public void setBeans(List<FinanceInvalidSupplierBean> beans) {
		this.beans = beans;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	public void setApplicationTime(String applicationTime) {
		// this.applicationTime = applicationTime;
	}

	public void setMemberCode(String memberCode) {
		this.memberCode = memberCode;
	}

	public boolean save() {
		boolean success = false;

		Connection conn = null;
		PreparedStatement invalidPst = null;
		PreparedStatement mergerPst = null;
		PreparedStatement stopInclassPst = null;
		PreparedStatement stopSupplierPst = null;

		PreparedStatement erpDeletePst = null;
		PreparedStatement erpInsertPst = null;

		PreparedStatement queryInclassPst = null;
		PreparedStatement logPst = null;
		ResultSet rs = null;

		try {

			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
			conn.setAutoCommit(false);

			invalidPst = conn.prepareStatement(STOPINVCLASS);
			logPst = conn.prepareStatement(LOGSQL);

			erpDeletePst = conn.prepareStatement(ERPDELETESQL);

			erpInsertPst = conn.prepareStatement(ERPINSERTSQL);

			List<String> tempCodeList = new ArrayList<String>();

			if (beans != null && beans.size() > 0) {
				for (int i = 0, size = beans.size(); i < size; i++) {
					FinanceInvalidSupplierBean bean = beans.get(i);
					// 失效
					// update fy_supplier_vendor_invclass v set v.denddate = ?
					// where v.pk_corp = ? and v.pk_vendor = ( select
					// v1.pk_vendor from fy_supplier_vendor v1 where v1.cvencode
					// = ? and (v1.dr is null or v1.dr = 0) ) and v.pk_invclass
					// = ?
					invalidPst.setString(1, bean.getDate());
					invalidPst.setString(2, bean.getPk_corp());
					invalidPst.setString(3, bean.getCode());
					// invalidPst.setString(4, NONEINVCLASS);

					invalidPst.addBatch();

					logPst.setString(1, String
							.valueOf(UUIDLong
									.longUUID()));
					logPst.setString(2, bean.getCode());
					logPst.setString(3, bean.getPk_corp());
					logPst.setString(4, "停用");
					logPst.setString(5, bean.getRemark());
					logPst.setString(6, "OA供应商失效申请表");
					logPst.setString(7, number);
					logPst.setString(8, uapUserID);
					logPst.setString(9, MyUtil.getNowStr());
					logPst.setString(10, MyUtil.getTodayDateStr());
					logPst.setString(11, memberCode);
					logPst.addBatch();

					erpDeletePst.setString(1, bean.getCode());
					erpDeletePst.addBatch();

					if (tempCodeList.indexOf(bean.getCode()) == -1) {
						erpInsertPst.setString(1, bean.getCode());
						erpInsertPst.addBatch();
						tempCodeList.add(bean.getCode());
					}
				}

				logPst.executeBatch();

				invalidPst.executeBatch();

				erpDeletePst.executeBatch();
				erpInsertPst.executeBatch();

				conn.commit();
				success = true;
			}
			if (!success) {
				throw new Exception();
			}

		} catch (Exception e) {
			success = false;
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("《供应商失效申请单》导入UPA异常，单据编号：" + number, e);
		} finally {
			DBUtil.setAutoCommit(conn, true);

			DBUtil.close(invalidPst);

			DBUtil.close(queryInclassPst);

			DBUtil.close(stopInclassPst);
			DBUtil.close(stopSupplierPst);

			DBUtil.close(logPst);

			DBUtil.close(erpDeletePst);
			DBUtil.close(erpInsertPst);

			DBUtil.close(rs, mergerPst, conn);
		}

		return success;
	}

	public boolean writeToErp() {
		return true;
	}

}
